package bupt.party.online.dao.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateTemplate;

import bupt.party.online.dao.ILearnRecordDao;
import bupt.party.online.dto.BranchStudyRecordDTO;
import bupt.party.online.dto.PartyStudyRecordDTO;
import bupt.party.online.dto.PersonStudyRecordDTO;
import bupt.party.online.model.Branch;
import bupt.party.online.model.LearnRecord;
import bupt.party.online.util.BuptPartyConfig;

public class LearnRecordDaoImpl implements ILearnRecordDao{
	
	private HibernateTemplate hibernateTemplate;
	

	public HibernateTemplate getHibernateTemplate() {
		return hibernateTemplate;
	}


	public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {
		this.hibernateTemplate = hibernateTemplate;
	}


	public LearnRecord findLearnRecordByID(int id) {
		// TODO Auto-generated method stub
		String queryString = "from LearnRecord a where a.id=" + id;
		List<LearnRecord> list=hibernateTemplate.find(queryString);
		if(list.size()==0){
			return null;
		}
		else{
			return list.get(0);
		}
	}


	public int getResultCount() {
		// TODO Auto-generated method stub
		int count = 0;
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		
		try{
			String sql = "select count(studentNo) from LearnRecord";
			
			Query query = session.createQuery(sql);
			
			count = ((Long)query.uniqueResult()).intValue();
			
			return count;
			
		}catch(Exception e)
		{
			e.printStackTrace();
			return 0;
		}
	}


	public List<LearnRecord> getResultsByPage(int firstResult, int pageSize) {
		
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		try{
			
			String sql = "from LearnRecord  order by visiteDate desc";
			Query query = session.createQuery(sql);
			query.setFirstResult(firstResult);
			query.setMaxResults(pageSize);
			
			return query.list();
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}


	public boolean saveOrUpdate(LearnRecord learnRecord) {
		try{
		this.hibernateTemplate.saveOrUpdate(learnRecord);	
		return true;
		}catch(Exception e){
			return false;
		}
		
	}


	public List<LearnRecord> findAllLearnRecordByStudentNO(String studentNo) {
		String sql = "from LearnRecord where  studentNo="+studentNo;
		List<LearnRecord> list = this.hibernateTemplate.find(sql);
		return list;
	}


	@SuppressWarnings("unchecked")
	@Override
	public List<LearnRecord> QueryByConditions(String queryString,
			Object[] values, int firstResult, int pageSize) {
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		try{
			Query q = session.createQuery(queryString);
			
			if(values != null){
				for(int i=0;i<values.length;i++){
					q.setParameter(i, values[i]);
				}
			}
			
			q.setFirstResult(firstResult);
			
			q.setMaxResults(pageSize);
			
			return q.list();
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		
	}


	@Override
	public List<PersonStudyRecordDTO> queryPersonLearnRecordByYear(
			Set<Integer> branch_ids, String year, String studentNO,
			String studentName, Integer firstpa, Integer secondpa,
			int pageNow,int pageSize) {
		
		List<PersonStudyRecordDTO> personStudyList = new ArrayList<PersonStudyRecordDTO>();
		
		String start =  year + BuptPartyConfig.START_MONTH_DATE;
		String end = year + BuptPartyConfig.END_MONTH_DATE;
		
		
		String branch_ids_toString = "";//先拼一下成为字符串
		String sub_branch_ids = "";
		
		if(branch_ids.size()>0){
			
			for(Iterator<Integer> itor= branch_ids.iterator(); itor.hasNext();) {
				   Integer i = (Integer)itor.next();
				   branch_ids_toString+= i +",";
				}
			//去掉最后的“，”
			sub_branch_ids = branch_ids_toString.substring(0,branch_ids_toString.length()-1);
		}
		
		
		String sql ="";
		
		
		
		if(firstpa!=-1){//输入查询条件
			
			if(secondpa!=-1){//选择了支部.
				
				sql = "select branch_name,id,student_name,student_no,sum(study_time) as studytime   from (" +
						"select a.student_no,a.student_name,a.id,a.branch_name,study_time  from (   " +
						"select d.student_no,d.student_name,branch.id,branch.branch_name from (" +
						"select student_no,student_name,student_branchId  from student where student_branchId in("+secondpa+") " +
						" and  student_name  like '%"+studentName+"%'" +
						" and  student_no  like '%"+studentNO+"%' ) as d " +
						" left join branch on  d.student_branchId=branch.id )as a   " +
						"left join learn_record on  learn_record.student_no=a.student_no    " +
						"and  visite_date>'"+start+"' " +
						"and visite_date<'"+end +"'     " +
						" )as  b group by  student_no  order by student_no asc";
				
				
			}else{//secondpa=-1 选择的是党委
				
				String sql2 = "from Branch where isDelete=0 and parentId="+firstpa;
				List<Branch> list = this.hibernateTemplate.find(sql2);
				
				Set<Integer> idList = new HashSet<Integer>();
				 
				//将查到的branchlist的 所有id拼起来
				for(int i=0;i<list.size();i++){
					idList.add(list.get(i).getId());
				}
				
				String ids_toString = "";
				for(Iterator<Integer> itor= idList.iterator(); itor.hasNext();) {
					   Integer i = (Integer)itor.next();
					   ids_toString+= i +",";
					}
				//去掉最后的“，”
				String ids ="";
				if(idList.size()>0){
					ids = ids_toString.substring(0,ids_toString.length()-1);
				}else{
					ids = "-1";
				}
				
				sql = "select branch_name,id,student_name,student_no,sum(study_time) as studytime   from (" +
						"select a.student_no,a.student_name,a.id,a.branch_name,study_time  from (   " +
						"select d.student_no,d.student_name,branch.id,branch.branch_name from (" +
						"select student_no,student_name,student_branchId  from student where student_branchId in("+ids+") " +
						" and  student_name  like '%"+studentName+"%'" +
						" and  student_no  like '%"+studentNO+"%' ) as d " +
						" left join branch on  d.student_branchId=branch.id )as a   " +
						"left join learn_record on  learn_record.student_no=a.student_no    " +
						"and  visite_date>'"+start+"' " +
						"and visite_date<'"+end +"'     " +
						" ) as b  group by  student_no  order by student_no asc";
				
				
			}
			
			
		}else{//当输入 name 或者  NO
			 
			if(firstpa==-1 &&(studentNO.trim().length()!=0 || studentName.trim().length()!=0)){
				
				sql = "select branch_name,id,student_name,student_no,sum(study_time) as studytime   from (" +
						"select a.student_no,a.student_name,a.id,a.branch_name,study_time  from (   " +
						"select d.student_no,d.student_name,branch.id,branch.branch_name from (" +
						"select student_no,student_name,student_branchId  from student where student_branchId in("+sub_branch_ids+") " +
						" and  student_name  like '%"+studentName+"%'" +
						" and  student_no  like '%"+studentNO+"%' ) as d " +
						" left join branch on  d.student_branchId=branch.id )as a   " +
						"left join learn_record on  learn_record.student_no=a.student_no    " +
						"and  visite_date>'"+start+"' " +
						"and visite_date<'"+end +"'     " +
						" ) as b  group by  student_no  order by student_no asc";
				
				
			}else{//什么也没有输入的时候。
				
				sql = "select branch_name,id,student_name,student_no,sum(study_time) as studytime   from (" +
						"select a.student_no,a.student_name,a.id,a.branch_name,study_time  from (   " +
						"select d.student_no,d.student_name,branch.id,branch.branch_name from (" +
						"select student_no,student_name,student_branchId  from student where student_branchId in("+sub_branch_ids+") " +
						" and  student_name  like '%"+studentName+"%'" +
						" and  student_no  like '%"+studentNO+"%' ) as d " +
						" left join branch on  d.student_branchId=branch.id )as a   " +
						"left join learn_record on  learn_record.student_no=a.student_no    " +
						"and  visite_date>'"+start+"' " +
						"and visite_date<'"+end +"'     " +
						" ) as b  group by  student_no  order by student_no asc";
				
				
			}
			
		}
		
		
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		
		try{
			Query q = session.createSQLQuery(sql);
			
			int firstResult = (pageNow-1)*pageSize;
			if(pageSize!=0){//当pageSize！=0的时候查询某一页的记录数
				q.setFirstResult(firstResult);
				q.setMaxResults(pageSize);
			}
			//当pageSize==0的时候，查询出的是所有的记录列表
			List list = q.list();
			
			if(list!=null &&list.size()>0){
				for(int i=0;i<list.size();i++){
					Object[] obj = (Object[])list.get(i);
					
					PersonStudyRecordDTO  personStudyRecordDTO =new PersonStudyRecordDTO();
					personStudyRecordDTO.setBranchName((String)obj[0]);//支部名
					personStudyRecordDTO.setBranchId((Integer)obj[1]);//支部id
					personStudyRecordDTO.setStudentName((String)obj[2]);//姓名
					personStudyRecordDTO.setStudentNo((String)obj[3]);//学号
					personStudyRecordDTO.setStudyTime(obj[4]==null?0.0:(Double)obj[4]);//学习时长
					personStudyRecordDTO.setYear(year);//年份
					
					personStudyList.add(personStudyRecordDTO);
				}
				
				return personStudyList;
				
			}else{
				return null;
			}
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
			
		
	}


	@Override
	public int getStudentCountByCondotions(Set<Integer> branch_ids,
			String year, String studentNO, String studentName, Integer firstpa,
			Integer secondpa) {
		
		int  count = 0;
		
		String start =  year + BuptPartyConfig.START_MONTH_DATE;
		String end = year + BuptPartyConfig.END_MONTH_DATE;
		
		
		String branch_ids_toString = "";//先拼一下成为字符串
		String sub_branch_ids = "";
		
		if(branch_ids.size()>0){
			
			for(Iterator<Integer> itor= branch_ids.iterator(); itor.hasNext();) {
				   Integer i = (Integer)itor.next();
				   branch_ids_toString+= i +",";
				}
			//去掉最后的“，”
			sub_branch_ids = branch_ids_toString.substring(0,branch_ids_toString.length()-1);
		}
		
		
		String sql ="";
		
		
		if(firstpa!=-1){//输入查询条件
			
			if(secondpa!=-1){//选择了支部.
				
				sql = "select count(*)  from (select student_no  from (  " +
					"select a.student_no,a.student_name,a.id,a.branch_name,study_time  from ( " +
					"select d.student_no,d.student_name,branch.id,branch.branch_name from  (  " +
					"select student_no,student_name,student_branchId  from " +
					" student where student_branchId in("+secondpa+")  " +
					" and  student_name  like '%"+studentName+"%'" +
					" and  student_no  like '%"+studentNO+"%' ) as d " +
					"left join branch on  d.student_branchId=branch.id  )as a " +
					"left join learn_record on  learn_record.student_no=a.student_no  " +
					"and  visite_date>'"+start+"' " +
					"and visite_date<'"+end +"'     " +
					") as b  group by  student_no) as c ";
				
				
			}else{//secondpa=-1 选择的是党委
				
				String sql2 = "from Branch where isDelete=0 and parentId="+firstpa;
				List<Branch> list = this.hibernateTemplate.find(sql2);
				
				Set<Integer> idList = new HashSet<Integer>();
				 
				//将查到的branchlist的 所有id拼起来
				for(int i=0;i<list.size();i++){
					idList.add(list.get(i).getId());
				}
				
				String branch_list = "";
				for(Iterator<Integer> itor= idList.iterator(); itor.hasNext();) {
					   Integer i = (Integer)itor.next();
					   branch_list+= i +",";
					}
				//去掉最后的“，”
				String sub_ids ="";
				if(idList.size()>0){
					sub_ids = branch_list.substring(0,branch_list.length()-1);
				}else{
					sub_ids = "-1";
				}
				
				
				sql = "select count(*)  from (select student_no  from (  " +
						"select a.student_no,a.student_name,a.id,a.branch_name,study_time  from ( " +
						"select d.student_no,d.student_name,branch.id,branch.branch_name from  (  " +
						"select student_no,student_name,student_branchId  from " +
						" student where student_branchId in("+sub_ids+")  " +
						" and  student_name  like '%"+studentName+"%'" +
						" and  student_no  like '%"+studentNO+"%' ) as d " +
						"left join branch on  d.student_branchId=branch.id  )as a " +
						"left join learn_record on  learn_record.student_no=a.student_no  " +
						"and  visite_date>'"+start+"' " +
						"and visite_date<'"+end +"'     " +
						") as b  group by  student_no) as c";
				
			}
			
			
		}else{//当输入 name 或者  NO
			 
			if(firstpa==-1 &&(studentNO.trim().length()!=0 || studentName.trim().length()!=0)){
				
				sql = "select count(*)  from (select student_no  from (  " +
						"select a.student_no,a.student_name,a.id,a.branch_name,study_time  from ( " +
						"select d.student_no,d.student_name,branch.id,branch.branch_name from  (  " +
						"select student_no,student_name,student_branchId  from " +
						" student where student_branchId in("+sub_branch_ids+")  " +
						" and  student_name  like '%"+studentName+"%'" +
						" and  student_no  like '%"+studentNO+"%' ) as d " +
						"left join branch on  d.student_branchId=branch.id  )as a " +
						"left join learn_record on  learn_record.student_no=a.student_no  " +
						"and  visite_date>'"+start+"' " +
						"and visite_date<'"+end +"'     " +
						") as b  group by  student_no) as c ";
				
				
			}else{//什么也没有输入的时候。
				
				sql = "select count(*)  from (select student_no  from (  " +
						"select a.student_no,a.student_name,a.id,a.branch_name,study_time  from ( " +
						"select d.student_no,d.student_name,branch.id,branch.branch_name from  (  " +
						"select student_no,student_name,student_branchId  from " +
						" student where student_branchId in("+sub_branch_ids+")  ) as d " +
						"left join branch on  d.student_branchId=branch.id  )as a " +
						"left join learn_record on  learn_record.student_no=a.student_no  " +
						"and  visite_date>'"+start+"' " +
						"and visite_date<'"+end +"'     " +
						") as b  group by  student_no) as c";
				
				
			}
			
			
		}
		
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		
		try{
			Query q = session.createSQLQuery(sql);
			
			count = ((BigInteger) q.uniqueResult()).intValue();
			
			
			return count;
			
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}
	}


	@Override
	public int getBranchCountByCondotions(Set<Integer> branch_ids, String year) {
		
		int  count = 0;
		
		String start =  year + BuptPartyConfig.START_MONTH_DATE;
		String end = year + BuptPartyConfig.END_MONTH_DATE;
		
		
		String branch_ids_toString = "";//先拼一下成为字符串
		String sub_branch_ids = "";
		
		if(branch_ids.size()>0){
			
			for(Iterator<Integer> itor= branch_ids.iterator(); itor.hasNext();) {
				   Integer i = (Integer)itor.next();
				   branch_ids_toString+= i +",";
				}
			//去掉最后的“，”
			sub_branch_ids = branch_ids_toString.substring(0,branch_ids_toString.length()-1);
		}
		
		
		
		String sql = "select count(*) from   " +
				        "(select  b.id from  " +
				        "(select  a.id,a.branch_name,student.student_no   from ( " +
				        "select id,branch_name  from  branch  where  is_delete=0 and level=3  and id in("+sub_branch_ids+") ) as a " +
				        "left join student  on  student.student_branchId=a.id  and  student.is_delete=0  and student.student_branchId in("+sub_branch_ids+")  )  as b " +
				        "left join learn_record  on learn_record.student_no=b.student_no " +
				        "and  visite_date>'"+start+"' " +
						"and visite_date<'"+end +"'   " +
						"group by b.id) as d  ";
				
		
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		
		try{
			Query q = session.createSQLQuery(sql);
			
			count = ((BigInteger) q.uniqueResult()).intValue();
			
			
			return count;
			
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}
	}


	@Override
	public List<BranchStudyRecordDTO> getBranchStudyRecordDTOByConditions(
			Set<Integer> branch_ids, String year, int pageNow, int pageSize) {
	
		List<BranchStudyRecordDTO> branchStudyList = new ArrayList<BranchStudyRecordDTO>();
		
		String start =  year + BuptPartyConfig.START_MONTH_DATE;
		String end = year + BuptPartyConfig.END_MONTH_DATE;
		
		
		String branch_ids_toString = "";//先拼一下成为字符串
		String sub_branch_ids = "";
		
		if(branch_ids.size()>0){
			
			for(Iterator<Integer> itor= branch_ids.iterator(); itor.hasNext();) {
				   Integer i = (Integer)itor.next();
				   branch_ids_toString+= i +",";
				}
			//去掉最后的“，”
			sub_branch_ids = branch_ids_toString.substring(0,branch_ids_toString.length()-1);
		}
		
		
		String sql = " select  b.id ,b.branch_name,count(distinct  b.student_no) as student_count,sum(study_time) as  branch_studytime from  " +
					"(select  a.id,a.branch_name,student.student_no   from ( " +
					"select id,branch_name  from  branch  where  is_delete=0 and level=3 and id in("+sub_branch_ids+") ) as a " +
					"left join student  on  student.student_branchId=a.id  and  student.is_delete=0  and student.student_branchId in("+sub_branch_ids+")  )  as b " +
					"left join learn_record  on learn_record.student_no=b.student_no " +
					"and  visite_date>'"+start+"' " +
					"and visite_date<'"+end +"'   " +
					" group by b.id  order by branch_name" ;
				
				
		
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		
		try{
			Query q = session.createSQLQuery(sql);
			
			int firstResult = (pageNow-1)*pageSize;
			if(pageSize!=0){//当pageSize！=0的时候查询某一页的记录数
				q.setFirstResult(firstResult);
				q.setMaxResults(pageSize);
			}
			//当pageSize==0的时候，查询出的是所有的记录列表
			List list = q.list();
			
			
			if(list!=null &&list.size()>0){
				for(int i=0;i<list.size();i++){
					Object[] obj = (Object[])list.get(i);
					
					BranchStudyRecordDTO  branchStudyRecordDTO =new BranchStudyRecordDTO();
					branchStudyRecordDTO.setId((Integer)obj[0]);//支部id
					branchStudyRecordDTO.setName((String)obj[1]);//支部名称
					branchStudyRecordDTO.setStudentCount(Integer.parseInt(((BigInteger)obj[2]).toString()));//支部人数
					branchStudyRecordDTO.setStutyTimeSum(obj[3]==null?0.0:(Double)obj[3]);//总学习时长
					double average = 0.0;
					if(obj[3]!=null && Integer.parseInt(((BigInteger)obj[2]).toString())!=0){
						average = (Double)obj[3]/Integer.parseInt(((BigInteger)obj[2]).toString());
					}
					
					branchStudyRecordDTO.setAverageTime(((int)(average*1000))/1000.0);//人均完成时间
					//这里我们目前设置为一个常量。
					double participateRate = 0.0;
					branchStudyRecordDTO.setParticipateRate(participateRate);
					
					branchStudyRecordDTO.setYear(year);//年份
					
					branchStudyList.add(branchStudyRecordDTO);
				}
				
				return branchStudyList;
				
			}else{
				return null;
			}
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		
		
	}


	@Override
	public List getStudentStudyTimeByBranchId(int branchId,String year) {
		List list;
		
		String start =  year + BuptPartyConfig.START_MONTH_DATE;
		String end = year + BuptPartyConfig.END_MONTH_DATE;
		
		
		String sql = "select sum(learn_record.study_time) as time  "+
				"FROM student,branch,learn_record  where  student.student_no=learn_record.student_no " +
				"and  student.student_branchid=branch.id  " +
				"and  student.is_delete=0   " +
				"and  branch.is_delete=0  " +
				"and  visite_date>'"+start+"' " +
				"and visite_date<'"+end +"'   " +
				"and  student.student_branchid  in('"+branchId+"')    " +
				" group by   learn_record.student_no";
		
		
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		
		try{
			
			Query q = session.createSQLQuery(sql);
			
			list = q.list();
			
			return list;
			
			}catch(Exception e){
				e.printStackTrace();
				return null;
			}
		
	}


	@Override
	public List getStudentStudyTimeByPartyId(Set<Integer> branch_ids, String year) {
		
		List list;
		
		String start =  year + BuptPartyConfig.START_MONTH_DATE;
		String end = year + BuptPartyConfig.END_MONTH_DATE;
		
		
		String branch_ids_toString = "";//先拼一下成为字符串
		String sub_branch_ids = "";//目标
		
		if(branch_ids.size()>0){
			
			for(Iterator<Integer> itor= branch_ids.iterator(); itor.hasNext();) {
				   Integer i = (Integer)itor.next();
				   branch_ids_toString+= i +",";
				}
			//去掉最后的“，”
			sub_branch_ids = branch_ids_toString.substring(0,branch_ids_toString.length()-1);
		}
		
		//当拼接出来的支部的串是空的时候，防止sql语句抛异常，我们设置为"-1"
		if("".equals(sub_branch_ids)) sub_branch_ids = "-1";
		
		String sql = "select sum(learn_record.study_time) as time  "+
				"FROM student,branch,learn_record  where  student.student_no=learn_record.student_no " +
				"and  student.student_branchid=branch.id  " +
				"and  student.is_delete=0  " +
				"and  branch.is_delete=0  " +
				"and  visite_date>'"+start+"' " +
				"and visite_date<'"+end +"'   " +
				"and  student.student_branchid  in("+sub_branch_ids+")    " +
				" group by   learn_record.student_no";
		
		
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		
		try{
			
			Query q = session.createSQLQuery(sql);
			
			list = q.list();
			
			return list;
			
			}catch(Exception e){
				e.printStackTrace();
				return null;
			}
	}


	@Override
	public int getPartyCountByCondotions(Set<Integer> branch_ids, String year) {
		
		int  count = 0;
		
		String start =  year + BuptPartyConfig.START_MONTH_DATE;
		String end = year + BuptPartyConfig.END_MONTH_DATE;
		
		
		String branch_ids_toString = "";//先拼一下成为字符串
		String sub_branch_ids = "";
		
		if(branch_ids.size()>0){
			
			for(Iterator<Integer> itor= branch_ids.iterator(); itor.hasNext();) {
				   Integer i = (Integer)itor.next();
				   branch_ids_toString+= i +",";
				}
			//去掉最后的“，”
			sub_branch_ids = branch_ids_toString.substring(0,branch_ids_toString.length()-1);
		}
		
		String sql = "select count(*) from " +
						"(select  t2.id from " +
						"(select id,branch_name from branch  where  is_delete=0  and  level=2)  as t2 " +
						"left  join" +
						"(select branch_name,id,student_name,student_no,sum(study_time) as studytime  , parent_id from (" +
						"select a.student_no,a.student_name,a.id,a.branch_name,study_time,parent_id  from (   " +
						"select d.student_no,d.student_name,branch.id,branch.branch_name, parent_id from (" +
						"select student_no,student_name,student_branchId  from student where student_branchId in("+sub_branch_ids+") " +
						") as d " +
						"left join branch on  d.student_branchId=branch.id )as a  " +
						"left join learn_record on  learn_record.student_no=a.student_no    " +
						"and  visite_date>'"+start+"' " +
						"and visite_date<'"+end +"'  " +
						") as b " +
						"group by  student_no ) as t1 " +
						"on  t2.id=t1.parent_id       group by t2.id  )  as  t3   ";
						
				
		
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		
		try{
			Query q = session.createSQLQuery(sql);
			
			count = ((BigInteger) q.uniqueResult()).intValue();
			
			
			return count;
			
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}
	}


	@Override
	public List<PartyStudyRecordDTO> getPartyStudyRecordDTOByConditions(
			Set<Integer> party_ids, String year, int pageNow, int pageSize) {
		
		List<PartyStudyRecordDTO> partyStudyList = new ArrayList<PartyStudyRecordDTO>();
		
		String start =  year + BuptPartyConfig.START_MONTH_DATE;
		String end = year + BuptPartyConfig.END_MONTH_DATE;
		
		
		//首先根据传递过来的分党委集合party_ids，获得分党委下面的所有所有支部的集合。（等价于显然获得是所有的三级党支部）
		
		String sql2 = "from Branch where isDelete=0 and level=3";
		List<Branch> branchList = this.hibernateTemplate.find(sql2);
		
		Set<Integer> branch_ids = new HashSet<Integer>();
		 
		//将查到的所有‘党支部’的 id拼起来
		for(int i=0;i<branchList.size();i++){
			branch_ids.add(branchList.get(i).getId());
		}
		
		
		String branch_ids_toString = "";//先拼一下成为字符串
		String sub_branch_ids = "";
		
		if(branch_ids.size()>0){
			
			for(Iterator<Integer> itor= branch_ids.iterator(); itor.hasNext();) {
				   Integer i = (Integer)itor.next();
				   branch_ids_toString+= i +",";
				}
			//去掉最后的“，”
			sub_branch_ids = branch_ids_toString.substring(0,branch_ids_toString.length()-1);
		}
		
		//这里b2是为了获得组织名称的。根据b1的父亲是b2 获得。
		String sql = "select  t2.id,t2.branch_name,count(distinct student_no)  as student_count,sum(studytime) as party_studytime from " +
					"(select id,branch_name from branch  where  is_delete=0  and  level=2)  as t2 " +
					"left  join" +
					"(select branch_name,id,student_name,student_no,sum(study_time) as studytime  , parent_id from ( " +
					"select a.student_no,a.student_name,a.id,a.branch_name,study_time,parent_id  from (   " +
					"select d.student_no,d.student_name,branch.id,branch.branch_name, parent_id from ( " +
					"select student_no,student_name,student_branchId  from student where student_branchId in("+sub_branch_ids+")  " +
					" ) as d " +
					"left join branch on  d.student_branchId=branch.id )as a  " +
					"left join learn_record on  learn_record.student_no=a.student_no   " +
					"and  visite_date>'"+start+"' " +
					"and visite_date<'"+end +"' " +
					") as b " +
					"group by  student_no ) as t1 " +
					"on  t2.id=t1.parent_id       group by t2.id  order by t2.branch_name "; 
					
		
		Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
		
		try{
			Query q = session.createSQLQuery(sql);
			
			int firstResult = (pageNow-1)*pageSize;
			if(pageSize!=0){//当pageSize！=0的时候查询某一页的记录数
				q.setFirstResult(firstResult);
				q.setMaxResults(pageSize);
			}
			//当pageSize==0的时候，查询出的是所有的记录列表
			List list = q.list();
			
			
			if(list!=null &&list.size()>0){
				for(int i=0;i<list.size();i++){
					Object[] obj = (Object[])list.get(i);
					
					PartyStudyRecordDTO  partyStudyRecordDTO =new PartyStudyRecordDTO();
					partyStudyRecordDTO.setId((Integer)obj[0]);//支部id
					partyStudyRecordDTO.setName((String)obj[1]);//支部名称
					partyStudyRecordDTO.setStudentCount(obj[2]==null?0:Integer.parseInt(((BigInteger)obj[2]).toString()));//支部人数
					partyStudyRecordDTO.setStutyTimeSum(obj[3]==null?0.0:(Double)obj[3]);//总学习时长
					
					double average = 0.0;
					if(obj[3]!=null && Integer.parseInt(((BigInteger)obj[2]).toString())!=0){
						average = (Double)obj[3]/Integer.parseInt(((BigInteger)obj[2]).toString());
					}
			
					partyStudyRecordDTO.setAverageTime(((int)(average*1000))/1000.0);//人均完成时间
					//这里我们目前设置为一个常量。
					double participateRate = 0.0;
					partyStudyRecordDTO.setParticipateRate(participateRate);
					
					partyStudyRecordDTO.setYear(year);//年份
					
					partyStudyList.add(partyStudyRecordDTO);
				}
				
				return partyStudyList;
				
			}else{
				return null;
			}
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
		
		
		
}
			
			
	
